﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.Odbc;
using System.Runtime.CompilerServices;

namespace infoMngmntGUI.Control
{
    /// <summary>
    /// Provides a static interface for interfacing with the remote database.  The other pages/classes in the 
    /// application utilize the static methods in this class to perform operations on the database.
    /// </summary>
    static class DbConnection
    {
        #region Private Data Members

        private static string connString =
            "DRIVER={MySQL ODBC 5.1 Driver};" +
            "SERVER=cs.westga.edu;" +
            "PORT=3307;" +
            "DATABASE=wcarrol1;" +
            "UID=wcarrol1;" +
            "PASSWORD=wc@5642";

        private static OdbcConnection myConnection = new OdbcConnection(connString);

        [MethodImpl(MethodImplOptions.Synchronized)]
        private static void closeDbConnection() { myConnection.Close(); }

        [MethodImpl(MethodImplOptions.Synchronized)]
        private static void openDbConnection() { myConnection.Open(); }

        [MethodImpl(MethodImplOptions.Synchronized)]
        private static OdbcDataReader executeQuery(OdbcCommand myCommand) { return myCommand.ExecuteReader(); }

        #endregion

        #region Public Methods

        /// <summary>
        /// Queries the database with a given userName and returns back the type of user enum (NURSE, 
        /// DOCTOR, or ADMIN) associated with the given user name.  The user type is used to determine 
        /// which Page controls can be accessed for a given login.  
        /// </summary>
        /// <param name="userName">The username entered into the login prompt</param>
        /// <returns>An enum representing the type of user (NURSE, DOCTOR, or ADMIN)</returns>
        public static AccessManagement.UserType GetPermissionType(string userName)
        {
            OdbcCommand myCommand = new OdbcCommand("SELECT permission FROM `LOGIN` WHERE loginID = '" + userName + "'", myConnection);
            AccessManagement.UserType permission = AccessManagement.UserType.NOTLOGGEDIN;

            openDbConnection();
            OdbcDataReader myReader = executeQuery(myCommand);
            if (myReader.HasRows)
            {
                Enum.TryParse<AccessManagement.UserType>(myReader.GetString(0), out permission);
            }

            myReader.Close();
            closeDbConnection();
            return permission;
        }

        public static String getPatientName(int pID)
        {
            string commandString = "SELECT fName, lName FROM `PATIENT` WHERE pID = '" + pID + "'";
            string name = ""; 
             try
            {
                OdbcConnection myConnection = new OdbcConnection(connString);
                myConnection.Open();

                OdbcCommand myCommand = new OdbcCommand(commandString, myConnection);
                OdbcDataReader myReader = executeQuery(myCommand);
                 

                while (myReader.Read())
                {
                    name+=myReader.GetString(0) + " " +myReader.GetString(1);
                }
                myReader.Close();
                myConnection.Close();

            }
            catch (OdbcException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return name;
        }

        

        public static DataTable getVisits(int pID)
        {
            string commandString ="SELECT vID, d.lName, n.lName, vDate, symptoms FROM `VISIT`, EMPLOYEE d, EMPLOYEE n WHERE d.eID = doctorID AND n.eID = nurseID AND pID = '" + pID + "'";


            string[] columnNames = { "Visit ID", "Doctor", "Nurse", "Date", "Symptoms" };

            DataTable dataTable = new DataTable("Query Table");

           


            try
            {
                OdbcConnection myConnection = new OdbcConnection(connString);
                myConnection.Open();

                OdbcCommand myCommand = new OdbcCommand(commandString, myConnection);
                OdbcDataReader myReader = executeQuery(myCommand);

                int columnCount = myReader.VisibleFieldCount;

                for (int i = 0; i < columnCount; i++)
                {
                    DataColumn column = new DataColumn(columnNames[i], myReader.GetFieldType(i));
                    dataTable.Columns.Add(column);
                }

                while (myReader.Read())
                {
                    Object[] objectData = new Object[columnCount];
                    myReader.GetValues(objectData);
                    dataTable.Rows.Add(objectData);
                }

                myReader.Close();
                myConnection.Close();

                return dataTable;

            }
            catch (OdbcException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Adds a new tuple entry to the PATIENT table in the database.  The attributes for the new 
        /// tuple are stored as a List of strings.  The strings are concatenated together to form an INSERT 
        /// query statement, and the statement is executed.
        /// </summary>
        /// <param name="attributes">The collection of attributes for the new patient tuple being added</param>
        /// <returns>Returns true if the patient is successfully added to the table, false otherwise</returns>
        public static bool AddPatient(List<string> attributes)
        {
            try
            {
                string fName = attributes[0];
                string lName = attributes[1];
                string dob = attributes[2];
                string street = attributes[3];
                string city = attributes[4];
                string state = attributes[5];
                string zip = attributes[6];
                string phone = attributes[7];

                OdbcConnection myConnection = new OdbcConnection(connString);
                OdbcCommand myCommand = new OdbcCommand(
                    @"INSERT INTO PATIENT (`fName`, `lName`, `dob`, `street`, `city`, `state`, `zip`, `phone`) VALUES " +
                    "('" + fName + "', '" + lName + "', '" + dob + "', '" + street +
                    "', '" + city + "', '" + state + "', " + zip + ", " + phone + ");", myConnection);

                myConnection.Open();
                OdbcDataReader myReader = executeQuery(myCommand);

                myReader.Close();
                myConnection.Close();
                return true;
            }
            catch (OdbcException)
            {
                Console.WriteLine("An OdbcException has occured.\nCheck that the query is a valid SQL statement.");
                return false;
            }
            catch (Exception ex)
            {
                Console.WriteLine("An exception has occured.\n" + ex.Message);
                return false;
            }
        }

        /// <summary>
        /// Queries the database to check that the user attemping to login has the corresponding login credientials in the table.
        /// Selects User_Name and Password attributes from the LoginTest table.  If the query returns a record, then the User_Name
        /// string is returned.
        /// </summary>
        /// <param name="userName">The username to query against the Login table</param>
        /// <param name="password">The password to query against the Login table</param>
        /// <returns>Null if the login credentials are not present in the table, otherwise the username is returned</returns>
        public static string Login(string userName, string password)
        {
            OdbcCommand myCommand = new OdbcCommand("SELECT `loginID` FROM `LOGIN` WHERE loginID = '" + userName + "' AND password = '" + password + "'", myConnection);
            string result = null;

            openDbConnection();
            OdbcDataReader myReader = executeQuery(myCommand);
            if (myReader.HasRows)
            {
                result = myReader.GetString(0);
            }

            myReader.Close();
            closeDbConnection();
            return result;
        }

        public static void AddTest(int vID, string testName )
        {
            string commandString =
                    "INSERT INTO `TEST` (`vID`, `testName`) VALUES (" + vID + ",'" + testName + "')";

            try
            {
                OdbcConnection myConnection = new OdbcConnection(connString);
                myConnection.Open();

                OdbcCommand myCommand = new OdbcCommand(commandString, myConnection);
                OdbcDataReader myReader = executeQuery(myCommand);
            }
            catch (OdbcException e)
            {
                Console.WriteLine("An OdbcException has occured.\nCheck that the query is a valid SQL statement.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("An exception has occured.\n" + ex.Message);
            }

        }

        /// <summary>
        /// Performs a simple query on the PATIENT table in the database.  The query can consist of either first and last
        /// name of patient, last name and DOB of patient, or just the DOB of a patient.  Depending on the search criteria, 
        /// the command string is constructed and executed on the DB.  The results of the query are returned back to the 
        /// calling class as a List of strings.
        /// </summary>
        /// <param name="attributes">The list of attributes to query the database with</param>
        /// <returns>A list of strings representing the results of the query on the database</returns>
        public static DataTable SearchPatients(List<string> attributes)
        {
            
                string commandString =
                    "SELECT pID, fName, lName, dob, phone FROM PATIENT ";

                if (attributes.Count == 1)
                {
                    string dob = attributes[0];
                    commandString += "WHERE dob='" + dob + "'";
                }
                else
                {
                    if (attributes[1].Contains("-"))
                    {
                        string lName = attributes[0];
                        string dob = attributes[1];
                        commandString += "WHERE lName='" + lName + "' AND dob='" + dob + "'";
                    }
                    else
                    {
                        string fName = attributes[0];
                        string lName = attributes[1];
                        commandString += "WHERE fName='" + fName + "' AND lname='" + lName + "'";
                    }
                }
                DataTable result = Query(commandString);
                if (result.Rows.Count > 0)
                {
                    result.Columns[0].ColumnName = "ID";
                    result.Columns[1].ColumnName = "First Name";
                    result.Columns[2].ColumnName = "Last Name";
                    result.Columns[3].ColumnName = "DOB";
                    result.Columns[4].ColumnName = "Phone";
                }

                return result;
        }

        public static DataTable getTests(int vID)
        {

            string commandString =
                "SELECT testName, test_result FROM TEST WHERE vID = '" + vID +"'";

            
            DataTable result = Query(commandString);
            if (result.Rows.Count > 0)
            {
                result.Columns[0].ColumnName = "Test Name";
                result.Columns[1].ColumnName = "Test Result";
            }

            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static List<string> GetPatients()
        {
            List<string> results = new List<string>();
            try
            {
                OdbcConnection myConnection = new OdbcConnection(connString);
                myConnection.Open();

                string commandString =
                    "SELECT pid, lname, fName FROM PATIENT ";

                OdbcCommand myCommand = new OdbcCommand(commandString, myConnection);
                OdbcDataReader myReader = executeQuery(myCommand);
                while (myReader.Read())
                {
                    results.Add(myReader.GetString(0) + "\t" +
                                myReader.GetString(1) + "\t" +
                                myReader.GetString(2));
                }
            }
            catch (OdbcException)
            {
                Console.WriteLine("An OdbcException has occured.\nCheck that the query is a valid SQL statement.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("An exception has occured.\n" + ex.Message);
            }

            return results;
        }

        /// <summary>
        /// Get the nurses
        /// </summary>
        /// <returns></returns>
        public static List<string> GetNurses()
        {
            return GetEmployeesByTypes(AccessManagement.UserType.NURSE);
        }

        /// <summary>
        /// Get the Doctors
        /// </summary>
        /// <returns></returns>
        public static List<string> GetDoctors()
        {
            return GetEmployeesByTypes(AccessManagement.UserType.DOCTOR);
        }

        /// <summary>
        /// Get the Employees by Type
        /// </summary>
        /// <returns></returns>
        public static List<string> GetEmployeesByTypes(AccessManagement.UserType type)
        {
            List<string> results = new List<string>();
            try
            {
                OdbcConnection myConnection = new OdbcConnection(connString);
                myConnection.Open();

                string commandString =
                    "SELECT e.eID, lname, fName FROM EMPLOYEE e, LOGIN l WHERE permission = '" + type + "' AND l.eID = e.eID";

                OdbcCommand myCommand = new OdbcCommand(commandString, myConnection);
                OdbcDataReader myReader = executeQuery(myCommand);
                while (myReader.Read())
                {
                    results.Add(myReader.GetString(0) + "\t" +
                                myReader.GetString(1) + "\t" +
                                myReader.GetString(2));
                }
            }
            catch (OdbcException)
            {
                Console.WriteLine("An OdbcException has occured.\nCheck that the query is a valid SQL statement.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("An exception has occured.\n" + ex.Message);
            }

            return results;
        }

        /// <summary>
        /// Executes a query on the database from the administration interface.  The query results are entered
        /// into a DataTable object (if the query itself contains a SELECT statement), the DataTable is 
        /// populated and returned back to the Admin Page to populate a DataGrid control
        /// </summary>
        /// <param name="sqlStatement">The SQL query to execute on the database</param>
        /// <returns>A DataTable populated with the results of the query</returns>
        public static DataTable Query(string sqlStatement)
        {
            DataTable dataTable = new DataTable("Query Table");

            try
            {
                OdbcConnection myConnection = new OdbcConnection(connString);
                myConnection.Open();

                OdbcCommand myCommand = new OdbcCommand(sqlStatement, myConnection);
                OdbcDataReader myReader = executeQuery(myCommand);

                int columnCount = myReader.VisibleFieldCount;
                for (int i = 0; i < columnCount; i++)
                {
                    DataColumn column = new DataColumn(myReader.GetName(i), myReader.GetFieldType(i));

                    if (dataTable.Columns.Contains(column.ColumnName))
                    {
                        column.ColumnName += "2";
                    }
                    dataTable.Columns.Add(column);
                }

                while (myReader.Read())
                {
                    Object[] objectData = new Object[columnCount];
                    myReader.GetValues(objectData);
                    dataTable.Rows.Add(objectData);
                }

                myReader.Close();
                myConnection.Close();

                return dataTable;

            }
            catch (OdbcException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }
}
